<?php
class YYKPDO{
	private static $instance;
	private $conn = array();//数据库连接句柄

	private $config;		//数据库配置
	private $currentSrv=0;	//当前(默认)服务器(配置编号)
	private $currentDb;	//当前(默认)数据库名

	private $currentTab;	//当前访问数据表

	private $currentOper;	//当前操作 select/update/delete/insert
	private $currentWhere;	//当前操作条件
	private $currentSet;		//update操作时要set的部分
	private $currentOrder;	//order子句
	private $currentGroup;	//group子句
	private $currentHaving;	//having子句
	private $currentField;	//所需字段
	private $currentLimit;
	private $lastSql;
	private $lastData;

	private $filterRank = 0;	//当前数据库链接 字符串过滤 等级
	//0:不进行任何过滤; 1:将换行替换为<br>; 2:进行html标签过滤; 9:严格的防xss过滤

	// 单例方法
	public static function create(){
		if (!isset(self::$instance)) {
			$c = __CLASS__;
			self::$instance = new $c;
		}
		return self::$instance;
	}

	private function __construct(){
		$this->config = YYK::$config['PDO'];
	}

	public function connect($no){
		$no = is_numeric($no) ? $no : 0;

		try{
			$this->conn[$no] = new PDO($this->config[$no]['dsn'], $this->config[$no]['username'], $this->config[$no]['password']);
			$this->charset($this->config['common']['charset']);
			return self::$instance;
		}
		catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
		}
		/**/
	}

	private function clearParam(){
		$this->currentTab	= null;
		$this->currentOper	= null;	 	//当前操作 select/update/delete/insert
		$this->currentWhere	= null;	//当前操作条件
		$this->currentSet	= null;		//update操作时要set的部分
		$this->currentOrder	= null;	//order子句
		$this->currentGroup	= null;	//group子句
		$this->currentHaving	= null;	//having子句
		$this->currentField	= null;	//所需字段
		$this->currentLimit = null;
	}

	public function server($no){
		$no = is_numeric($no) ? $no : 0;
		$this->currentSrv = $no;
		return self::$instance;
	}

	public function setDb($dbname){
		$this->conn[$this->currentSrv]->exec('use '. $dbname);//  ??  inject
		$this->currentDb = $dbname;
		return self::$instance;
	}

	public function charset($charset){
		$this->conn[$this->currentSrv]->exec('set names '. $charset);//  ??  inject
		return self::$instance;
	}

	public function beginTransaction(){
		if (!isset($this->conn[$this->currentSrv]) || !($this->conn[$this->currentSrv])) {
			$this->connect($this->currentSrv);
		}
		return $this->conn[$this->currentSrv]->beginTransaction();
	}

	public function commit(){
		return $this->conn[$this->currentSrv]->commit();
	}

	public function rollBack(){
		return $this->conn[$this->currentSrv]->rollBack();
	}

	public function table($tabName){
		$this->currentTab = $tabName;
		return self::$instance;
	}

	public function field($fieldName){
		if (is_array($fieldName)) {
			$this->currentField = implode(',', $fieldName);
		}
		else
			$this->currentField = $fieldName;
		return self::$instance;
	}

	public function where($where){
		$this->currentWhere = $where;
		return self::$instance;
	}

	public function order($order){
		$this->currentOrder = $order;
		return self::$instance;
	}

	public function group($group){
		$this->currentGroup = $group;
		return self::$instance;
	}

	public function limit($limit){
		$this->currentLimit = $limit;
		return self::$instance;
	}
	//构造select语句
	private function constructSelect(){
		$sql = 'select ';
		if (count($this->currentField)) {
			$sql .= $this->currentField;
		}
		else{
			$sql .= '*';
		}

		$sql .= ' from ' . $this->currentTab ;

		if ((is_array($this->currentWhere) && count($this->currentWhere)) || strlen($this->currentWhere) ) {
			$sql.= ' where ';
			if (is_array($this->currentWhere)){
				foreach ($this->currentWhere as $key => $value) {
					$sql .= '`'.$key . '`' . '=:' . $key . ' and ';
				}
				$sql = substr($sql, 0, strlen($sql)-5);
			}
			else
				$sql .= $this->currentWhere;
		}

		//group
		if (strlen($this->currentGroup)) {
			$sql .= ' group by ' . $this->currentGroup;
		}

		//having
		if (strlen($this->currentHaving)) {
			$sql .= ' having ' . $this->currentHaving;
		}

		//order
		if (strlen($this->currentOrder)) {
			$sql .= ' order by ' . $this->currentOrder;
		}

		//limit
		if (strlen($this->currentLimit)) {
			$sql .= ' limit ' . $this->currentLimit;
		}

		//var_dump($sql);
		$this->lastSql = $sql;

		if (!isset($this->conn[$this->currentSrv]) || !($this->conn[$this->currentSrv])) {
			$this->connect($this->currentSrv);
		}
		$sth = $this->conn[$this->currentSrv]->prepare($sql);	//, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)
		//var_dump($this->currentWhere);
		$this->lastData = $this->currentWhere;
		if (is_array($this->currentWhere)) {
			$sth->execute($this->currentWhere);
		}
		else{
			$sth->execute();
		}
		return $sth;
	}

	public function select(){
		$sth = $this->constructSelect();
		$re = $sth->fetchAll(PDO::FETCH_ASSOC);
		$sth->closeCursor();

		//清空当前字段
		$this->clearParam();
		return $re;
	}

	//取一行，返回一维数组
	public function getLine(){
		$sth = $this->constructSelect();
		$re = $sth->fetch(PDO::FETCH_ASSOC);
		$sth->closeCursor();

		//清空当前字段
		$this->clearParam();
		return $re;
	}

	//取一个字段，返回简单变量
	public function getField($fieldName){
		$sth = $this->constructSelect();
		$re = $sth->fetch(PDO::FETCH_ASSOC);
		$sth->closeCursor();

		//清空当前字段
		$this->clearParam();
		return $re[$fieldName];
	}
	public function getFiled($fieldName){
		return $this->getField($fieldName);
	}

	//
	public function count(){
		$this->currentField = 'count(*)';
		$sth = $this->constructSelect();
		$re = $sth->fetch(PDO::FETCH_NUM);
		$sth->closeCursor();

		//清空当前字段
		$this->clearParam();
		return $re[0];
	}

	//insert
	public function insert($data){
		$sql = 'insert into `' . $this->currentTab . '` ( ';
		if (is_array($data)){
			foreach ($data as $key => $value) {
				$sql .= '`' . $key . '`' . ' ,';
			}
			$sql = rtrim($sql, ',');
			$sql .= ') values(';
			foreach ($data as $key => &$value) {
				$sql .= ':' . $key . ' ,';
				$value = $this->filter($value);
			}
			$sql = rtrim($sql, ',');
			$sql .= ')';
		}
		$this->lastSql = $sql;
		$this->lastData = $data;

		try{
			if (!isset($this->conn[$this->currentSrv]) || !($this->conn[$this->currentSrv])) {
				$this->connect($this->currentSrv);
			}
			$sth = $this->conn[$this->currentSrv]->prepare($sql);
			$sth->execute($data);
			$rows = $sth->rowCount ();
			$sth->errorInfo();
		}
		catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
		}
		$lastId = $this->conn[$this->currentSrv]->lastInsertId();
		$result = $lastId?$lastId:$rows;
		$this->clearParam();
		return $result;
	}


	public function update($data){
		$sql = 'update ' . $this->currentTab . ' set ';

		if (is_array($data)){
			foreach ($data as $key => $value) {
				$sql .= '`' . $key . '`=:' . $key . ' ,';
				$data[$key] = $this->filter($value);
			}
			$sql = rtrim($sql, ',');
			$sql .= ' ';
		}

		if ((is_array($this->currentWhere) && count($this->currentWhere)) || strlen($this->currentWhere) ) {
			$sql.= 'where ';
			if (is_array($this->currentWhere)){
				foreach ($this->currentWhere as $key => $value) {
					$sql .= $key . '=:w_' . $key . ' and ';
					$data['w_'.$key] = $value;
				}
				$sql = substr($sql, 0, strlen($sql)-5);
			}
			else
				$sql .= $this->currentWhere;
		}
		//var_dump($data);
		$this->lastSql = $sql;
		$this->lastData = $data;
		//echo $sql;
		//var_dump($data);

		if (!isset($this->conn[$this->currentSrv]) || !($this->conn[$this->currentSrv])) {
			$this->connect($this->currentSrv);
		}
		$sth = $this->conn[$this->currentSrv]->prepare($sql);
		$sth->execute($data);
		$this->clearParam();
		return $sth->rowCount();
		//exit;
	}

	public function delete(){
		$sql = 'delete from ' . $this->currentTab ;

		if ((is_array($this->currentWhere) && count($this->currentWhere)) || strlen($this->currentWhere) ) {
			$sql.= ' where ';
			if (is_array($this->currentWhere)){
				foreach ($this->currentWhere as $key => $value) {
					$sql .= $key . '=:w_' . $key . ' and ';
					$data['w_'.$key] = $value;
				}
				$sql = substr($sql, 0, strlen($sql)-5);
			}
			else{
				$sql .= $this->currentWhere;
				$data = [];
			}
		}

		//echo $sql;
		//var_dump($data);

		if (!isset($this->conn[$this->currentSrv]) || !($this->conn[$this->currentSrv])) {
			$this->connect($this->currentSrv);
		}
		$sth = $this->conn[$this->currentSrv]->prepare($sql);
		$sth->execute($data);
		$this->clearParam();
		return $sth->rowCount();
	}

	/*
	*直接执行sql语句
	*
	*return select返回结果集，insert成功返回最后插入id
	*		其他情况返回受影响条数
	*/
	public function query($sql,$data=array()){
		$this->lastSql = $sql;
		$this->lastData = $data;
		if (!isset($this->conn[$this->currentSrv]) || !($this->conn[$this->currentSrv])) {
			$this->connect($this->currentSrv);
		}
		if(is_array($data)){
			$sth = $this->conn[$this->currentSrv]->prepare($sql);
			$sth->execute($data);
		}else{
			$sth = $this->conn[$this->currentSrv]->query($sql);
		}
		if(strtolower(substr($sql,0,6))=='select'){
			$re = $sth->fetchAll(PDO::FETCH_ASSOC);
			/*
			while($result=$sth->fetch(PDO::FETCH_ASSOC)){
				$re[]=$result;
			}
			if(stripos($sql,'count')&&!stripos($sql,'order by')&&!stripos($sql,'group by')&&!stripos($sql,'limit ')){
				$re = $re[0];
			}
			*/
		}else{
			$re = $sth->rowCount();
			if(strtolower(substr($sql,0,6))=='insert'){
				$lastId='';
				$lastId = $this->conn[$this->currentSrv]->lastInsertId();
				$re = $lastId?$lastId:$re;
			}
		}
		$sth->closeCursor();
		return $re;
	}


	public function setInc($field, $num=1){
		$sql = 'update `' . $this->currentTab . '` set `' . $field . '`=`'. $field .'`+'.$num;

		if ((is_array($this->currentWhere) && count($this->currentWhere)) || strlen($this->currentWhere) ) {
			$sql.= ' where ';
			if (is_array($this->currentWhere)){
				foreach ($this->currentWhere as $key => $value) {
					$sql .= $key . '=:w_' . $key . ' and ';
					$data['w_'.$key] = $value;
				}
				$sql = substr($sql, 0, strlen($sql)-5);
			}
			else
				$sql .= $this->currentWhere;
		}
		//var_dump($data);
		$this->lastSql = $sql;
		$this->lastData = $data;
		//echo $sql;
		//var_dump($data);

		if (!isset($this->conn[$this->currentSrv]) || !($this->conn[$this->currentSrv])) {
			$this->connect($this->currentSrv);
		}
		$sth = $this->conn[$this->currentSrv]->prepare($sql);
		$this->clearParam();
		$sth->execute($data);
	}

	//count
	//max/min/distinct

	public function last(){
		var_dump($this->lastSql);
		var_dump($this->lastData);
	}

	//设置字符串过滤等级
	public function setFilterRank($rank){
		$this->filterRank = $rank;
	}

	//获取字符串过滤等级
	public function getFilterRank(){
		return $this->filterRank ;
	}

	//字符串过滤,防XSS
	/*
	private function filter(&$str){
		$str = htmlspecialchars($str);
		return $str;
	}
	*/

	private function filter(&$val) {
		switch ($this->filterRank) {
			case 9:
				// remove all non-printable characters. CR(0a) and LF(0b) and TAB(9) are allowed
				// this prevents some character re-spacing such as <java\0script>
				// note that you have to handle splits with \n, \r, and \t later since they *are* allowed in some inputs
				$val = preg_replace('/([\x00-\x08,\x0b-\x0c,\x0e-\x19])/', '', $val);

				// straight replacements, the user should never need these since they're normal characters
				// this prevents like <IMG SRC=@avascript:alert('XSS')>
				$search = 'abcdefghijklmnopqrstuvwxyz';
				$search .= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
				$search .= '1234567890!@#$%^&*()';
				$search .= '~`";:?+/={}[]-_|\'\\';
				for ($i = 0; $i < strlen($search); $i++) {
					// ;? matches the ;, which is optional
					// 0{0,7} matches any padded zeros, which are optional and go up to 8 chars

					// @ @ search for the hex values
					$val = preg_replace('/(&#[xX]0{0,8}'.dechex(ord($search[$i])).';?)/i', $search[$i], $val); // with a ;
					// @ @ 0{0,7} matches '0' zero to seven times
					$val = preg_replace('/(&#0{0,8}'.ord($search[$i]).';?)/', $search[$i], $val); // with a ;
				}

				// now the only remaining whitespace attacks are \t, \n, and \r
				$ra1 = array('javascript', 'vbscript', 'expression', 'applet', 'meta', 'xml', 'blink', 'link', 'style', 'script', 'embed', 'object', 'iframe', 'frame', 'frameset', 'ilayer', 'layer', 'bgsound', 'title', 'base');
				$ra2 = array('onabort', 'onactivate', 'onafterprint', 'onafterupdate', 'onbeforeactivate', 'onbeforecopy', 'onbeforecut', 'onbeforedeactivate', 'onbeforeeditfocus', 'onbeforepaste', 'onbeforeprint', 'onbeforeunload', 'onbeforeupdate', 'onblur', 'onbounce', 'oncellchange', 'onchange', 'onclick', 'oncontextmenu', 'oncontrolselect', 'oncopy', 'oncut', 'ondataavailable', 'ondatasetchanged', 'ondatasetcomplete', 'ondblclick', 'ondeactivate', 'ondrag', 'ondragend', 'ondragenter', 'ondragleave', 'ondragover', 'ondragstart', 'ondrop', 'onerror', 'onerrorupdate', 'onfilterchange', 'onfinish', 'onfocus', 'onfocusin', 'onfocusout', 'onhelp', 'onkeydown', 'onkeypress', 'onkeyup', 'onlayoutcomplete', 'onload', 'onlosecapture', 'onmousedown', 'onmouseenter', 'onmouseleave', 'onmousemove', 'onmouseout', 'onmouseover', 'onmouseup', 'onmousewheel', 'onmove', 'onmoveend', 'onmovestart', 'onpaste', 'onpropertychange', 'onreadystatechange', 'onreset', 'onresize', 'onresizeend', 'onresizestart', 'onrowenter', 'onrowexit', 'onrowsdelete', 'onrowsinserted', 'onscroll', 'onselect', 'onselectionchange', 'onselectstart', 'onstart', 'onstop', 'onsubmit', 'onunload');
				$ra = array_merge($ra1, $ra2);

				$found = true; // keep replacing as long as the previous round replaced something
				while ($found == true) {
					$val_before = $val;
					for ($i = 0; $i < sizeof($ra); $i++) {
						$pattern = '/';
						for ($j = 0; $j < strlen($ra[$i]); $j++) {
							if ($j > 0) {
								$pattern .= '((&#[xX]0{0,8}([9ab]);)||(&#0{0,8}([9|10|13]);))*';
							}
							$pattern .= $ra[$i][$j];
						}
						$pattern .= '/i';
						$replacement = substr($ra[$i], 0, 2).'<x>'.substr($ra[$i], 2); // add in <> to nerf the tag
						$val = preg_replace($pattern, $replacement, $val); // filter out the hex tags
						if ($val_before == $val) {
							// no replacements were made, so exit the loop
							$found = false;
						}
					}
				}
			case 2:
				$val = htmlspecialchars($val, ENT_QUOTES);
				break;
			case 0://不进行任何过滤直接退出
				return $val;
		}
		$val = str_replace("\n", '<br>', $val);

		return $val;
	}
}
